--EXEC SP_GET_ALL_RUNNING_AND_HOLD_LOTS 'PRE_GRIND', 0, 0, 0
-- NEEDS TO MODIFY FOR WAFER QTY IN
	
ALTER PROC SP_GET_ALL_RUNNING_AND_HOLD_LOTS
( 
 @PROCESS_TYPE   VARCHAR(50),
 @PROCESSSTEP_ID INT,
 @LOCATION_ID INT,
 @SEARCH_LOT_ID INT
 
)
AS
BEGIN
	SET NOCOUNT ON
	CREATE TABLE #RESULTS ( LOT_ID INT,PROCESSSTEP_ID INT, LOCATION_ID INT, PART_NUMBER VARCHAR(50), Lot_Number VARCHAR(50), Fab_Lot_Number VARCHAR(50), WAFER_QTY_IN INT, QTY_IN INT, DATE_IN DATETIME, VENDOR_NAME VARCHAR(50), DATE_CODE VARCHAR(50), NEXT_VENDOR VARCHAR(50), NEXT_PROCESS VARCHAR(50), LOT_STATUS VARCHAR(50), LOT_COMMENTS VARCHAR(500) , WAFER_ID VARCHAR(100) ) 
	
	DECLARE @QUERYSTRING	VARCHAR(4000)
	DECLARE @BASE_PROCESS_NAME VARCHAR(50)

	SELECT @BASE_PROCESS_NAME = BASE_PROCESS_TYPE FROM PROCESS_TYPES_TABLE WHERE PROCESS_TYPE = @PROCESS_TYPE

	SET @QUERYSTRING = N'
 	INSERT INTO #RESULTS 
 	SELECT	LT.LOT_ID, LT.PROCESSSTEP_ID, LT.LOCATION_ID, PART_NUMBER, LOT_NUMBER, FAB_LOT_NUMBER, WAFER_QTY_IN, QTY_IN, DATE_IN, L_VENDOR_NAME, LOT_DATE_CODE, NULL, NULL, LOT_CURRENT_STATUS, LOT_COMMENTS, NULL
	FROM LOT_TABLE LT, PROCESS_STEP PS, LOCATION L, PRODUCT P 
	WHERE 
	LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND
	LT.LOCATION_ID = L.LOCATION_ID AND ( WAFER_QTY_IN > 0 OR QTY_IN > 0 ) AND LT.LOT_CURRENT_STATUS <> ''Close'' '
	
		IF @PROCESS_TYPE IS NOT NULL AND @PROCESS_TYPE <> '0'
	 	BEGIN
	 		SET @QUERYSTRING = @QUERYSTRING + ' AND PS_TYPE = '''+ @PROCESS_TYPE+''''
	 	END
	 	IF ( @PROCESSSTEP_ID IS NOT NULL AND @PROCESSSTEP_ID > 0  )
	 	BEGIN
	 		SET @QUERYSTRING = @QUERYSTRING +' AND PS.PROCESSSTEP_ID = ' + CONVERT(VARCHAR,@PROCESSSTEP_ID)
	 	END
		IF ( @LOCATION_ID IS NOT NULL AND @LOCATION_ID > 0  )
		BEGIN
	 		SET @QUERYSTRING = @QUERYSTRING +' AND LT.LOCATION_ID = ' + CONVERT(VARCHAR,@LOCATION_ID)
	 	END
	 	IF ( @SEARCH_LOT_ID IS NOT NULL AND @SEARCH_LOT_ID > 0  )
		BEGIN
 			SET @QUERYSTRING = @QUERYSTRING +' AND LT.LOT_ID = ' + CONVERT(VARCHAR,@SEARCH_LOT_ID)
 		END

		SET @QUERYSTRING = @QUERYSTRING + ' ORDER BY P.PART_NUMBER'
		--PRINT '@QUERYSTRING :- '+@QUERYSTRING
		EXEC (@QUERYSTRING)

	DECLARE @WIPCURSOR CURSOR
	DECLARE @LOT_NUMBER VARCHAR(50)
	DECLARE @LOT_ID INT
	DECLARE @NEXT_VENDOR_NAME VARCHAR(50)
	DECLARE @NEXT_PROCESS VARCHAR(50)
	DECLARE @VENDOR_NAME VARCHAR(50)
	DECLARE @WAFER_ID	 VARCHAR(100)

	SET @WIPCURSOR = CURSOR FOR SELECT LOT_NUMBER, LOT_ID, VENDOR_NAME FROM #RESULTS
		
	OPEN @WIPCURSOR
	FETCH NEXT FROM @WIPCURSOR INTO @LOT_NUMBER, @LOT_ID, @VENDOR_NAME

	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF @BASE_PROCESS_NAME = 'FAB'
		BEGIN
			  SELECT @WAFER_ID = WAFER_ID FROM FAB_LOT WHERE LOt_ID = @LOT_ID
		END
		IF @BASE_PROCESS_NAME = 'SORT'
		BEGIN
			  SELECT @WAFER_ID = SL_WAFER_ID FROM SORT_LOT WHERE LOt_ID = @LOT_ID
		END
		IF @BASE_PROCESS_NAME = 'WAFER STORE'
		BEGIN
			  SELECT @WAFER_ID = WAFER_ID FROM INVENTORY_LOT WHERE LOt_ID = @LOT_ID
		END
		SELECT @NEXT_PROCESS = PROCESS_TYPE FROM PROCESS_TYPES_TABLE WHERE SORTING_ORDER = (SELECT SORTING_ORDER+1 FROM PROCESS_TYPES_TABLE WHERE PROCESS_TYPE = @PROCESS_TYPE )

		UPDATE #RESULTS SET WAFER_ID = @WAFER_ID , NEXT_PROCESS = @NEXT_PROCESS WHERE LOT_ID = @LOT_ID
		
		SET @WAFER_ID = NULL
		SET @NEXT_PROCESS = NULL	
		SET @NEXT_VENDOR_NAME = NULL
		SET @LOT_NUMBER = NULL
		SET @LOT_ID		= NULL
		SET @VENDOR_NAME = NULL
		FETCH NEXT FROM @WIPCURSOR INTO @LOT_NUMBER, @LOT_ID, @VENDOR_NAME 
		
	END
	CLOSE @WIPCURSOR
	DEALLOCATE @WIPCURSOR
		
		SELECT 
			LOT_ID ,PROCESSSTEP_ID , LOCATION_ID , PART_NUMBER , Lot_Number , Fab_Lot_Number , WAFER_QTY_IN, QTY_IN , CONVERT(VARCHAR,DATE_IN, 101) AS DATE_IN , VENDOR_NAME, DATE_CODE, NEXT_VENDOR,NEXT_PROCESS, LOT_STATUS,LOT_COMMENTS, WAFER_ID
			FROM #RESULTS ORDER BY LOT_NUMBER
END